package com.example.zwlyz.database;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.example.zwlyz.bean.User;

import java.util.ArrayList;
import java.util.List;

public class DBHelper extends SQLiteOpenHelper {
    private static final String TAG = "DBHelper";
    private static final String DATABASE_NAME = "db_user_manager";// 数据库名
    private static final int DATABASE_VERSION = 1;// 数据库版本号
    private static final String TABLE_NAME_USER = "user"; //学生表
    private static final String TABLE_NAME_SETTINGS = "settings"; // 设置表
    private static DBHelper mHelper = null;
    private DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    //单例模式
    public static DBHelper getInstance(Context context){
        if(mHelper == null){
            mHelper = new DBHelper(context);
         }
        return mHelper;
    }

    //数据库创建时被调用
    @Override
    public void onCreate(SQLiteDatabase db) {
        initTable(db);
        initData(db);
    }

    //数据库升级
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (oldVersion < 2) { // 数据库 1 -> 2 升级
            // 创建设置表
            db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_SETTINGS + " (keycolumn text primary key,valuecolumn text)");
        }
    }

    private void initTable(SQLiteDatabase db) {
        // 创建用户表
        db.execSQL("CREATE TABLE IF NOT EXISTS " + TABLE_NAME_USER + " (" +
                "user_id integer primary key autoincrement," +
                "username text," +
                "password text)") ;
    }

    private void initData(SQLiteDatabase db) {
        db.execSQL("insert into " + TABLE_NAME_USER +
                " (username, password) " +
                "values" +
                "('张三', '03')");
        db.execSQL("insert into " + TABLE_NAME_USER +
                " (username, password) " +
                "values" +
                "('李四', '04')");
    }
    //添加用户信息
    public void insertUser(User user) {
        SQLiteDatabase db = getWritableDatabase();
        db.execSQL("insert into " + TABLE_NAME_USER +
                " (username, password) " +
                "values" +
                "('" + user.getUsername() + "','" + user.getPassword()+"')");
    }

    //添加学生
    public int insertUser2(User user) {
        //获取可写数据库
        SQLiteDatabase db = getWritableDatabase();

        //创建ContentValues以Key-Value的形式封装数据
        ContentValues values = new ContentValues();
        values.put("username", user.getUsername());
        values.put("password", user.getPassword());

        //执行insert方法，插入数据
        long newRowId = db.insert(TABLE_NAME_USER, null, values);
        Log.d(TAG, "insertUser2 newRowId = " + newRowId);
        return (int) newRowId;
    }

    //根据id删除用户
    public void deleteUserById(int id) {
        if (id >= 0) {
            SQLiteDatabase db = getReadableDatabase();
            int changedRowCount = db.delete(TABLE_NAME_USER, "user_id = ?", new String[]{String.valueOf(id)});
            Log.d(TAG, "deleteUserById changedRowCount = " + changedRowCount);
        }
    }

    //根据id修改用户名字
    public void updateUserNameById(String name, int id) {
        if (name != null && id >= 0) {
            SQLiteDatabase db = getReadableDatabase();
            ContentValues values = new ContentValues();
            values.put("username", name);
            int changedRowCount = db.update(TABLE_NAME_USER, values, "_id = ?", new String[] {String.valueOf(id)});
            Log.d(TAG, "updateUserNameById changedRowCount = " + changedRowCount);
        }
    }

    //查询所有用户
    @SuppressLint("Range")
    public List<User> selectAllUser() {
        List<User> list = new ArrayList<>();
        SQLiteDatabase db = getReadableDatabase();

        Cursor cursor = db.query(
                TABLE_NAME_USER,     // 表名
                null,                   // 要查询的列数据
                null,                   // 条件语句
                null,                   // 条件语句的值
                null,                   // group by
                null,                   // having
                null                    // 排序语句
        );
        if (cursor != null && cursor.getCount() > 0) {
            while (cursor.moveToNext()) {
                User user = new User();
                user.setUser_id(cursor.getInt(cursor.getColumnIndex("user_id")));
                user.setUsername(cursor.getString(cursor.getColumnIndex("username")));
                user.setPassword(cursor.getString(cursor.getColumnIndex("password")));
                list.add(user);
            }
        }
        Log.d(TAG, "selectAllUser = " + list.size());
        return list;
    }

    //根据id查询用户信息
    @SuppressLint("Range")
    public User getUserById(int id) {
        SQLiteDatabase db = getReadableDatabase();

        Cursor cursor = db.query(
                TABLE_NAME_USER,                                                        // 表名
                null,                                                                      // 要查询的列数据
                "_id = ?",                                                                 // 条件语句
                new String[] {String.valueOf(id)},                                         // 条件语句的值
                null,                                                                      // group by
                null,                                                                      // having
                null                                                                       // 排序语句
        );
        User user = null;
        if (cursor != null && cursor.getCount() > 0) {
            if (cursor.moveToFirst()) {
                user = new User();
                user.setUser_id(cursor.getInt(cursor.getColumnIndex("user_id")));
                user.setUsername(cursor.getString(cursor.getColumnIndex("username")));
                user.setPassword(cursor.getString(cursor.getColumnIndex("password")));
                Log.d(TAG, "current user = " + user.toString());
            }
        }

        return user;
    }

    //登录
    @SuppressLint("Range")
    public User login(String username, String password) {
        SQLiteDatabase db = getReadableDatabase();

        Cursor cursor = db.query(
                TABLE_NAME_USER,                                                           // 表名
                null,                                                                      // 要查询的列数据
                "username = ?  and password = ?",                                          // 条件语句
                new String[] {username, password},                                         // 条件语句的值
                null,                                                                      // group by
                null,                                                                      // having
                null                                                                       // 排序语句
        );
        User user = null;
        if (cursor != null && cursor.getCount() > 0) {
            if (cursor.moveToFirst()) {
                user = new User();
                user.setUser_id(cursor.getInt(cursor.getColumnIndex("user_id")));
                user.setUsername(cursor.getString(cursor.getColumnIndex("username")));
                user.setPassword(cursor.getString(cursor.getColumnIndex("password")));
                Log.d(TAG, "login ="+"id:"+user.getUser_id()+" 名字：" + user.getUsername().toString()+"密码："+user.getPassword().toString());
            }
        }
        return user;
    }
}
